const express = require('express');
const { handleError, executeQuery, formatDateTime } = require('../utils');
const router = express.Router();

// 查询目录列表
router.post('/list', async (req, res) => {
    const { page = 1, pageSize = 10, search = '' } = req.body;
    const offset = (page - 1) * pageSize;
    const limit = pageSize;

    const searchQuery = `%${search}%`;

    const totalQuery = `
        SELECT COUNT(*) AS total
        FROM JournalDirectories
        WHERE journal_name LIKE ? OR cn_number LIKE ?;
    `;

    const query = `
        SELECT *
        FROM JournalDirectories
        WHERE journal_name LIKE ? OR cn_number LIKE ?
        LIMIT ${limit} OFFSET ${offset};
    `;

    try {
        const totalResult = await executeQuery(totalQuery, [searchQuery, searchQuery]);
        const total = totalResult[0].total;
        const directories = await executeQuery(query, [searchQuery, searchQuery]);

        directories.forEach(directory => {
            directory.created_at = formatDateTime(directory.created_at);
            directory.updated_at = formatDateTime(directory.updated_at);
        });

        res.json({ total, page, pageSize, data: directories });
    } catch (error) {
        handleError(error, res);
    }
});

// 新增目录
router.post('/add', async (req, res) => {
    const { journal_name, cn_number, issn, postal_code, publication_cycle, publication_place, sponsor, details } = req.body;
    const query = `
        INSERT INTO JournalDirectories (journal_name, cn_number, issn, postal_code, publication_cycle, 
                                        publication_place, sponsor, details, created_at, updated_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW());
    `;

    try {
        await executeQuery(query, [journal_name, cn_number , issn || null, postal_code || null, publication_cycle || null, publication_place || null, sponsor || null, details || null]);
        res.json({ message: '目录添加成功' });
    } catch (error) {
        handleError(error, res);
    }
});

// 更新目录信息
router.post('/update', async (req, res) => {
    const { directory_id, journal_name, cn_number, issn, postal_code, publication_cycle, publication_place, sponsor, details } = req.body;
    const query = `
        UPDATE JournalDirectories
        SET journal_name = ?, cn_number = ?, issn = ?, postal_code = ?, publication_cycle = ?, 
            publication_place = ?, sponsor = ?, details = ?, updated_at = NOW()
        WHERE directory_id = ?;
    `;

    try {
        await executeQuery(query, [journal_name, cn_number , issn || null, postal_code || null, publication_cycle || null, publication_place || null, sponsor || null, details || null, directory_id]);
        res.json({ message: '目录更新成功' });
    } catch (error) {
        handleError(error, res);
    }
});

// 删除目录
router.post('/delete', async (req, res) => {
    const { directory_id } = req.body;
    const query = 'DELETE FROM JournalDirectories WHERE directory_id = ?;';

    try {
        await executeQuery(query, [directory_id]);
        res.json({ message: '目录删除成功' });
    } catch (error) {
        handleError(error, res);
    }
});

module.exports = router;
